﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DB;
using System.Data.SqlClient;
using CommonLibrary.DataModel.WorkFlow;
using System.Data;

namespace ServiceApplication.Dao.WorkFlow
{
    public class SpqxdybDao
    {
        public int AddEntity(SpqxdybEntity spqxdybEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@spqxdyb_id", spqxdybEntity.spqxdyb_id));
            parameterList.Add(new SqlParameter("@spqx_id", spqxdybEntity.spqx_id));
            parameterList.Add(new SqlParameter("@user_id", spqxdybEntity.user_id));


            string sql = "insert into wf_spqxdyb (spqx_id,user_id) values " +
                " (@spqx_id,@user_id); " +
                " select @@identity";
            string spqxdyb_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(spqxdyb_id);
        }

        public void DeleteBySpqxId(int spqx_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@spqx_id", spqx_id));
            string sql = "delete from wf_spqxdyb where spqx_id=@spqx_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void DeleteByUserId(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "delete from wf_spqxdyb where user_id=@user_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(SpqxdybEntity spqxdybEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@spqxdyb_id", spqxdybEntity.spqxdyb_id));
            parameterList.Add(new SqlParameter("@spqx_id", spqxdybEntity.spqx_id));
            parameterList.Add(new SqlParameter("@user_id", spqxdybEntity.user_id));

            string sql = "update wf_spqxdyb set spqx_id=@spqx_id,user_id=@user_id where spqxdyb_id=@spqxdyb_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public SpqxdybEntity QueryEntity(int spqxdyb_id)
        {
            SpqxdybEntity spqxdybEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@spqxdyb_id", spqxdyb_id));
            string sql = "select spqxdyb_id,spqx_id,user_id " +
                " from wf_spqxdyb where spqxdyb_id=@spqxdyb_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            spqxdybEntity = new SpqxdybEntity();
            spqxdybEntity.spqxdyb_id = int.Parse(result[0][0]);
            spqxdybEntity.spqx_id = int.Parse(result[0][1]);
            spqxdybEntity.user_id = int.Parse(result[0][2]);
            return spqxdybEntity;
        }

        public DataTable Query(string spqx_id, string user_id)
        {
            string sql = "select spqxdyb_id 编码,dyb.spqx_id 权限编码,s.spqx_name 权限名称,u.user_id 用户编码,u.user_name 用户名称 from wf_spqxdyb dyb,jc_user u,wf_spqx s " +
                             " where u.user_id=dyb.user_id and s.spqx_id=dyb.spqx_id and spqxdyb_id=spqxdyb_id ";

            List<SqlParameter> parameterList = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(spqx_id))
            {
                parameterList.Add(new SqlParameter("@spqx_id", spqx_id));
                sql += " and s.spqx_id=@spqx_id ";
            }
            if (!string.IsNullOrEmpty(user_id))
            {
                parameterList.Add(new SqlParameter("@user_id", user_id));
                sql += " and u.user_id=@user_id ";
            }
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryAll()
        {
            string sql = "select spqxdyb_id 编码,spqx_id 审批权限编码,user_id 用户编码 from wf_spqxdyb where spqxdyb_id=spqxdyb_id ";
            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }
    }
}
